import sqlite3
from sqlite3 import Error
import json
import numpy as np


class FaceDB:
    def __init__(self, db_file):
        try:
            self.conn = sqlite3.connect(db_file)
            print(f"SQLite版本: {sqlite3.version}")
        except Error as e:
            print(f"连接错误: {e}")
            self.conn = None
        self.create_table()
        
    def create_table(self):
        try:
            cursor = self.conn.cursor()
            cursor.execute('''CREATE TABLE IF NOT EXISTS table_faceid 
                            ( id INTEGER PRIMARY KEY, 
                              name TEXT NOT NULL, 
                              num INTEGER, 
                              feature TEXT NOT NULL)''')
            cursor.execute('''CREATE TABLE IF NOT EXISTS table_faceRecord 
                            ( id INTEGER PRIMARY KEY, 
                              path TEXT NOT NULL, 
                              name TEXT NOT NULL, 
                              coord TEXT NOT NULL, 
                              feature TEXT NOT NULL)''')
            self.conn.commit()
            print("表创建成功")
        except Error as e:
            print(f"建表错误: {e}")
            
    def insert_faceid(self, info):
        """插入人脸ID数据"""
        sql = '''INSERT INTO table_faceid (name, num, feature) VALUES(?,?,?)'''
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql, info)
            self.conn.commit()
            print(f"插入成功，ID: {cursor.lastrowid}")
            return cursor.lastrowid
        except Error as e:
            print(f"插入错误: {e}")
        return None
        
    def select_all_faceid(self):
        """查询所有face id"""
        try:
            cursor = self.conn.cursor()
            cursor.execute("SELECT * FROM table_faceid")
            rows = cursor.fetchall()
            #print("\n所有用户:")
            #for row in rows:
            #    print(row)
            #    print(np.array(json.loads(row[3])).shape)
            return rows
        except Error as e:
            print(f"查询错误: {e}")
            return []
    
    def get_faceid_max_id(self):
        cursor = self.conn.cursor()
        # 执行查询
        cursor.execute("SELECT MAX(id) FROM table_faceid")
        result = cursor.fetchone()
        #self.conn.close()
        # 处理结果（可能为None）
        if result[0] is None:
            return 0  # 若表为空，返回0
        return result[0]
        
    def insert_faceRecord(self, info):
        """插入人脸ID数据"""
        sql = '''INSERT INTO table_faceRecord (path, name, coord, feature) VALUES(?,?,?,?)'''
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql, info)
            self.conn.commit()
            print(f"插入成功，record: {cursor.lastrowid}")
            return cursor.lastrowid
        except Error as e:
            print(f"插入错误: {e}")
        return None
        
    def select_all_faceRecord(self):
        """查询所有用户"""
        try:
            cursor = self.conn.cursor()
            cursor.execute("SELECT * FROM table_faceRecord")
            rows = cursor.fetchall()
            return rows
        except Error as e:
            print(f"查询错误: {e}")
            return []
    
    def select_record_by_name(self, n):
        try:
            cursor = self.conn.cursor()
            cursor.execute(f"SELECT * FROM table_faceRecord WHERE name == {n};")
            rows = cursor.fetchall()
            return rows
        except Error as e:
            print(f"查询错误: {e}")
            return []
            
    def __exit__(self, exc_type, exc_val, exc_tb):
        # 退出with语句时执行，自动释放资源
        if self.cursor:
            self.cursor.close()
        if self.conn:
            if exc_type is None:
                self.conn.commit()  # 无异常时提交事务
            else:
                self.conn.rollback()  # 有异常时回滚
            self.conn.close()
        print("资源已自动关闭")
        return False  # 不抑制异常，让其正常抛出



if __name__ == '__main__':
    faceDB = FaceDB("face.db")
    float_array = [float(i) for i in range(128)]
    json_data = json.dumps(float_array) #列表才能被序列化
    faceDB.insert_faceid(('Alice', 25, json_data))
    faceDB.select_all_faceid()

